package DAO;

import Entity.Student;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DAO_Student {

    Connection conn = null;
    Statement state = null;
    ResultSet rs = null;

    public DAO_Student() {
        //connection1("jdbc:odbc:StudentManagementDatabase");
        connection4("jdbc:sqlserver://localhost"
                + ":1433;databaseName=StudentManagementDatabase","sa","123456");    }

    public final void connection1(String url) {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            try {
                conn = DriverManager.getConnection(url);
            } catch (SQLException ex) {
                Logger.getLogger(DAO_Student.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println("DAO Student is connected");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAO_Student.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public final void connection4(String url, String username, String password){
         try {
            //create driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            
            //get connection
            conn=DriverManager.getConnection(url, 
                    username, password);
            System.out.println("DAO Student is connected");
            
        } catch (ClassNotFoundException | SQLException ex) {
        }
    }   
    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Student.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Author: HieuTD
     *
     * @param person
     * @return number of affected rows
     */
    public int addStudent(Student person) {
        int n = 0;
        // prepare SQL statement
        String preSQL = "INSERT INTO Student (name, age, sex, hometown) VALUES (?,?,?,?)";

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            pre.setString(1, person.getName());
            pre.setInt(2, person.getAge());
            pre.setInt(3, person.getSex());
            pre.setString(4, person.getHometown());

            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        return n;
    }

    /**
     * Task: TramNN
     *
     * @param id
     * @return
     */
    public int deleteStudent(int id) {
        int n = 0;
        /*
         TODO: Write the code here
         1. Write SQL Statement
         2. Execute
         */
        String preSQL = "DELETE FROM Student WHERE id = " + id;

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        
        return n;
    }

    /**
     * Task for: TramNN
     * @param person
     * @return 
     */
    public int updateStudent(Student person) {
        int n = 0;
        // prepare SQL statement
        String preSQL = " UPDATE Student ";
        preSQL+= " SET name = ?, age = ?, sex = ?, hometown = ? ";
        preSQL+= " WHERE id = ? ";

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            pre.setString(1, person.getName());
            pre.setInt(2, person.getAge());
            pre.setInt(3, person.getSex());
            pre.setString(4, person.getHometown());
            pre.setInt(5, person.getId());

            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        return n;
    }
    
    public ResultSet search (String option, String input) {
        ResultSet rs = null;
        
        // prepare SQL statement
        String preSQL = " SELECT * FROM Student ";
        preSQL += " WHERE " + option + " LIKE ? ";
        
        try {            
            PreparedStatement pre = conn.prepareStatement(preSQL);
            
            if (option.equalsIgnoreCase("ID"))
                pre.setInt(1, Integer.parseInt(input));
            else {
                input = "%" + input + "%";
                pre.setString(1, input);
            }
            rs = pre.executeQuery();
        } catch (Exception ex) {
        }
        
        return rs;
    }

    /**
     * Author: HieuTD
     * @param sql
     * @return 
     */
    public ResultSet getData(String sql) {
        ResultSet dataset = null;
        try {
            state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            dataset = state.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Student.class.getName()).log(Level.SEVERE, null, ex);
        }

        return dataset;

    }
    
    /**
     * Author: HieuTD
     * @return 
     */
    public List<Student> studentsList() {
        List<Student> student = new ArrayList<>();
        try {
            String sql = "SELECT * FROM Student";
            ResultSet list = getData(sql);
            while (list.next()) {
                Student temp = new Student(list.getInt(1), list.getString(2), list.getInt(3), list.getInt(4), list.getString(5));
                student.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_Manager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return student;
    }
    
    /**
     * Author: HieuTD
     * @param from
     * @param to
     * @return 
     */
    public List<Student> listInPageRange(int from, int to) {
        List<Student> student = new ArrayList<>();
        try {
            String sql = "SELECT * FROM Student ORDER BY id ASC OFFSET  "+ (from-1) +" ROWS FETCH NEXT "+ (to+1) +" ROWS ONLY";
            ResultSet list = getData(sql);
            while (list.next()) {
                Student temp = new Student(list.getInt(1), list.getString(2), list.getInt(3), list.getInt(4), list.getString(5));
                student.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_Manager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return student;
    }    
    
    public int getNumberOfStudent() {
        int n = 0;
        try {
            String sql = "SELECT COUNT(id) FROM Student";
            ResultSet list = getData(sql);
            while(list.next()) {
                n = list.getInt(1);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_Manager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return n;
    }    
    /**
     * Just for test connection. Shift+F6 to run me!
     * @param args
     */
    public static void main(String[] args) {
        DAO_Student s = new DAO_Student();
        s.addStudent(new Student("Nguyễn Xuân Tài", 20, 1, "Nghệ An"));
    }
}
